﻿using CNative.Utilities;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace CNative.DbUtils
{
    public class SqlEntity
    {
        public SqlEntity(IDbHelper  dbHelper)
        {
            Parameters = new List<IDataParameter>();
            CommandType = CommandType.Text;

            DbHelper = dbHelper;
        }
        public SqlEntity(IDbHelper dbHelper, string sql)
        {
            Sql = sql;
            Parameters = new List<IDataParameter>();
            CommandType = CommandType.Text;

            DbHelper = dbHelper;
        }
        /// <summary>
        /// 数据库操作类型
        /// </summary>
        public IDbHelper DbHelper { get; }
        /// <summary>
        /// SQL脚本
        /// </summary>
        public string Sql { get; set; }
        /// <summary>
        /// 
        /// </summary>
        public CommandType CommandType { get; set; }
        /// <summary>
        /// 入参集合
        /// </summary>
        public List<IDataParameter> Parameters { get; set; }

        /// <summary>
        /// 添加参数
        /// </summary>
        /// <param name="propName">参数名称</param>
        /// <param name="val">参数值</param>
        /// <param name="sqlent"></param>
        /// <param name="parameterName"></param>
        /// <param name="tb"></param>
        /// <param name="paramSuffix">参数前缀</param>
        public virtual bool AddDbParameter(string propName, object val, out string parameterName, string paramSuffix = "wp_", DbTableInfo tb = null)
        {
            parameterName = "";
            if (this.Parameters == null) this.Parameters = new List<IDataParameter>();
            paramSuffix = paramSuffix.NullToStr() + this.Parameters.Count + "_";

            var para = DbHelper.SqlDbProvider.GetDbParameter(propName, val, paramSuffix, tb);
            if (para != null)
            {
                this.Parameters.Add(para);
                parameterName = para.ParameterName;
                return true;
            }
            return false;
        }
        /// <summary>
        /// 添加参数
        /// </summary>
        /// <param name="parameterName"></param>
        /// <param name="parameterValue"></param>
        /// <param name="direction"></param>
        /// <param name="valDBType"></param>
        public void AddParameter(string parameterName, object parameterValue, ParameterDirection direction = ParameterDirection.Input, DbType valDBType = DbType.String)
        {
            var param = DbHelper.SqlDbProvider.GetDbParameter(parameterName, parameterValue, "", null, direction, valDBType);
            if (param != null)
            {
                if (Parameters == null) Parameters = new List<IDataParameter>();
                Parameters.Add(param);
            }
        }
        /// <summary>
        /// 批量添加参数
        /// </summary>
        /// <param name="parameters"></param>
        public void AddParameters(params IDataParameter[] parameters)
        {
            if (parameters?.Length > 0)
            {
                if (Parameters == null) Parameters = new List<IDataParameter>();
                Parameters.AddRange(parameters);
            }
        }
        /// <summary>
        /// 添加参数
        /// </summary>
        /// <param name="parameters"></param>
        public void AddParameters(params CNDbParameter[] dbParameters)
        {
            if (dbParameters?.Length > 0)
            {
                if (Parameters == null) Parameters = new List<IDataParameter>();
                dbParameters.ForEach(parameter =>
                {
                    var param = DbHelper.SqlDbProvider.GetDbParameter(parameter.ParameterName, parameter.Value, "", null, parameter.Direction, parameter.DbType);
                    if (param != null)
                    {
                        if (Parameters == null) Parameters = new List<IDataParameter>();
                        Parameters.Add(param);
                    }
                });
            }
        }
    }

    public static class SqlEntityExp
    {
        public static bool Execute(this SqlEntity sql) => sql.DbHelper.Execute(sql);
        public static bool Execute(this List<SqlEntity> sqlList) => sqlList?.Count > 0 ? sqlList[0].DbHelper.Execute(sqlList) : false;
        public static DataSet QueryDataSet(this SqlEntity sql) => sql.DbHelper.QueryDataSet(sql);
        public static DataTable QueryDataTable(this SqlEntity sql) => sql.DbHelper.QueryDataTable(sql);
        public static List<T> Query<T>(this SqlEntity sql) where T : class => sql.DbHelper.Query<T>(sql);

        public static T GetSingle<T>(this SqlEntity sql) => sql.DbHelper.GetSingle<T>(sql);
        public static T GetSingleRow<T>(this SqlEntity sql) where T : class => sql.DbHelper.GetSingleRow<T>(sql);
        public static void ExecuteOneWay(this SqlEntity sql) => sql.DbHelper.ExecuteOneWay(sql);

        //--------------------------------------------------------------------------------------------------------------------------
        /// <summary>
        /// 是否只查询SQL脚
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        internal static bool IsSelectSQL(this SqlEntity sql)
        {
            if (sql.Sql.IsNullOrEmpty() || sql.CommandType != CommandType.Text) return false;
            var sqlLower = sql.Sql.ToLower();
            var result = System.Text.RegularExpressions.Regex.IsMatch(sqlLower, "[ ]*select[ ]") && !System.Text.RegularExpressions.Regex.IsMatch(sqlLower, "[ ]*insert[ ]|[ ]*update[ ]|[ ]*delete[ ]");
            return result;
        }

        public static string ToJoinSqlInVals<T>(this T[] array)
        {
            if (array == null || array.Length == 0)
            {
                return ToSqlValue(string.Empty);
            }
            else
            {
                return string.Join(",", array.Where(c => c != null).Select(it => it.ToSqlValue()));
            }
        }

        public static object ToSqlValue(this object value)
        {
            if (value != null && Constants.NumericalTypes.Contains(value.GetType()))
                return value;

            var str = value + "";
            return str.ToSqlValue();
        }

        public static string ToSqlValue(this string value)
        {
            return string.Format("'{0}'", value.ToSqlFilter());
        }

        /// <summary>
        ///Sql Filter
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        public static string ToSqlFilter(this string value)
        {
            if (!value.IsNullOrEmpty())
            {
                value = value.Replace("'", "''");
            }
            return value;
        }

    }

}
